package com.bruce.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Logger;

/**
 * Created by bruce on 18/12/13 -- 9:58
 */
public class PoiTest {
    private static Logger logger = Logger.getLogger("测试用例");

    public static void main(String[] args) throws IOException {
        File file = new File("C:\\Users\\bruce\\Desktop\\work\\移动统计\\data", "2018年2月领导月报.xls");
        ArrayList<Object> jsonList = readExcel(file);
        System.out.println(jsonList);
    }

    private static ArrayList<Object> readExcel(File file) throws IOException {
        ArrayList<Object> jsonString = new ArrayList<Object>();
        //Excel
        Workbook wb = WorkbookFactory.create(new FileInputStream(file));
        //sheet
        int sheetNum = wb.getNumberOfSheets();
        //循环sheet
        for (int i = 0; i < sheetNum; i++) {
            Sheet sheet = wb.getSheetAt(i);
            //
            readSheet(sheet);
        }
        wb.close();
        return jsonString;
    }

    private static void readSheet(Sheet sheet) {
        String sheetName = sheet.getSheetName();
        int lastRowNum = sheet.getLastRowNum();
        //当前sheet的json文件
        JSONObject sheetJson = new JSONObject();
        //当前sheet的array，作为sheetJson 的value值
        JSONArray sheetArr = new JSONArray();
        //sheet的第一行，获取作为json的key值
        JSONArray key = new JSONArray();
        // 循环行Row
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                break;
            }
            // 循环列Cell，在这里组合json文件
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            JSONObject rowJson = new JSONObject();
            for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                Cell cell = null;
                try {
                    cell = row.getCell(cellNum);
                    if (cell == null) {
                        rowJson.put(key.getString(cellNum), "");
                        continue;
                    }
                    if (rowNum == 0)
                        key.add(toString(cell));
                    else {
                        //若是列号超过了key的大小，则跳过
                        if (cellNum >= key.size()) continue;
                        rowJson.put(key.getString(cellNum), toString(cell));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (rowJson.keySet().size() > 0)
                sheetArr.add(rowJson);
        }
    }

    private static Object toString(Cell cell) {
        switch (cell.getCellType()) {
            case _NONE:
                cell.setCellType(CellType.STRING);
                return "";
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                    return sdf.format(cell.getDateCellValue());
                }
                cell.setCellType(CellType.STRING);
                return cell.getStringCellValue();
            case STRING:
                String val = cell.getStringCellValue();
                if ("无".equalsIgnoreCase(val)) return "";
                //将其中的map格式和数组格式的字符串，转化为相应的数据类型
                if (val.indexOf("[") > -1) {
                    val = val.substring(1, val.length() - 1);
                    String[] array = val.split(",");
                    return array;
                }
                return val;
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case BOOLEAN:
                return cell.getBooleanCellValue() + "";
            case ERROR:
                return "非法字符";
            default:
                return "未知字符";
        }
    }
}
